﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ServiceModel;
using System.Data.SqlClient;
using System.Net;

namespace AfvandingService
{
    [ServiceBehavior(InstanceContextMode=InstanceContextMode.Single)]
    public class Central : ICentral
    {
        #region Fields
        private List<Pumpestation> _pumpestationsliste;
        private const string CONNECTIONSTRING = "Data Source=10.165.150.52;Database=Afvandingsprojekt;Integrated Security=False;User ID=dm122;Password=dm122;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
        #endregion

        #region ctor
        public Central()
        {
            _pumpestationsliste = new List<Pumpestation>();
            HentPumpestationerSQL();
        } 
        #endregion

        #region Methods
        public bool AnmodStart(int pumpenr, int pumpestationsnr)
        {

            foreach (var pumpestation in _pumpestationsliste)
            {
                if (pumpestation.Pumpestationsnr == pumpestationsnr)
                {
                    pumpestation.AnmodningStartPumpe(pumpenr, pumpestation);
                    StartPumpe(pumpenr, pumpestation.Pumpestationsnr);
                    return true;
                }
            }
            return false;
        }

        public bool AnmodStop(int pumpenr, int pumpestationsnr)
        {
            foreach (var pumpestation in _pumpestationsliste)
            {
                if (pumpestation.Pumpestationsnr == pumpestationsnr)
                {
                    pumpestation.AnmodningStopPumpe(pumpenr, pumpestation);
                    StopPumpe(pumpenr, pumpestation.Pumpestationsnr);
                    return true;
                }
            }
            return false;
        }

        public void OpretPumpestation(int pumpestationsnr, string netværksaddresse, string IP, int port, string vandstandsrisiko, double vandstand)
        {
            Pumpestation pust = new Pumpestation(pumpestationsnr, netværksaddresse, IP, port, vandstandsrisiko, vandstand);
            _pumpestationsliste.Add(pust);
            OpretPumpestationSQL(pust);
        }

        public void SletPumpestation(int pumpestationsnr)
        {
            foreach (var item in _pumpestationsliste)
            {
                if (item.Pumpestationsnr == pumpestationsnr)
                {
                    _pumpestationsliste.Remove(item);
                    break;
                }
            }
            SletPumpeStationSQL(pumpestationsnr);
        }

        public void AnmodOpretPumpe(int pumpestationsnr, double pumpekapacitet, int pumpenr)
        {
            {
                foreach (var item in _pumpestationsliste)
                {
                    if (item.Pumpestationsnr == pumpestationsnr)
                    {
                        OpretPumpeSQL(pumpekapacitet, pumpenr, item.Pumpestationsnr, false);
                        //item.OpretPumpe(pumpekapacitet, pumpenr, item); -- SKAL KUN OPRETTES I DB
                    }
                }

            }
        }

        public void OpdatérPumpestation(int pumpestationsnr, string netværksaddresse, string IP, int port, string vandstandsrisiko, double vandstand)
        {
            foreach (var station in _pumpestationsliste)
            {
                if (station.Pumpestationsnr == pumpestationsnr)
                {
                    station.Netværksadresse = netværksaddresse;
                    station.IP = IP;
                    station.Port = port;
                    station.Vandstandsrisiko = vandstandsrisiko;
                    station.Vandstand = vandstand;
                    OpdatérPumpeStationSQL(pumpestationsnr, netværksaddresse, IP, port, vandstandsrisiko, vandstand);
                }
            }
        }

        public void AnmodOpdatérPumpe(Pumpestation pumpestation, Pumpe pumpe, double pumpekapacitet, int pumpenr)
        {
            pumpestation.RetPumpe(pumpe, pumpekapacitet, pumpenr);
        }

        public void AnmodSletPumpe(int pumpestationsnr, int pumpenr)
        {
            List<Pumpestation> pumpestationsListe = Pumpestationer();


            foreach (var pumpestation in pumpestationsListe)
            {
                if (pumpestation.Pumpestationsnr == pumpestationsnr)
                {
                    SletPumpeSQL(pumpestationsnr, pumpenr);
                }
            }
        }

        public double PumpestationVandstand(int pumpestationsnr)
        {
            // return PumpestationVandstand(pumpestation).ToString();

           return PumpestationVandstandSQL(pumpestationsnr);
        }

        public List<double> PumpestationerVandstand()
        {
            //List<string> vandstandsListe = new List<string>();
            //foreach (var pumpestation in _pumpestationsliste)
            //{
            //    vandstandsListe.Add(pumpestation.AnmodningVandstand());
            //}
            // return vandstandsListe;

            List<double> vandListe = new List<double>(); 
            vandListe = PumpestationerVandstandSQL();
            return vandListe;
        }

        public List<Pumpestation> Pumpestationer() 
        {
            return _pumpestationsliste;
        }

        public List<Pumpe> Pumper(int pumpestationsnr)
        {
            foreach (var pumpestation in _pumpestationsliste)
            {
                if (pumpestation.Pumpestationsnr == pumpestationsnr)
                {
                    return pumpestation.Pumper(pumpestationsnr);
                }
            }
            return null;
        }

        public Dictionary<double, DateTime> VandstandsLog(int pumpestationsNr)
        {
            return HentVandstandsLogSQL(pumpestationsNr);
        }

        #endregion
    
        #region SQL

        private void HentPumpestationerSQL() 
        {
            List<Pumpestation> TEMPpumpestationer = new List<Pumpestation>();

            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_HentPumpeStationer", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Pumpestation TEMPstation = new Pumpestation();

                    TEMPstation.Pumpestationsnr = (int)reader["PumpestationsNr"];
                    TEMPstation.Netværksadresse = reader["Netværksadresse"].ToString();
                    TEMPstation.IP = reader["IPadresse"].ToString();
                    TEMPstation.Port = (int) reader["Portnummer"];
                    TEMPstation.Vandstandsrisiko = reader["Vandstandsrisiko"].ToString();
                    TEMPstation.Vandstand = double.Parse(reader["Vandstand"].ToString());

                    TEMPpumpestationer.Add(TEMPstation);
                }
            }
            _pumpestationsliste = TEMPpumpestationer;
        }

        private void OpretPumpestationSQL(Pumpestation ps) 
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_OpretPumpeStation", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", ps.Pumpestationsnr);
                cmd.Parameters.AddWithValue("@Netværksadresse",ps.Netværksadresse);
                cmd.Parameters.AddWithValue("@IPadresse",ps.IP.ToString());
                cmd.Parameters.AddWithValue("@Portnummer",ps.Port);
                cmd.Parameters.AddWithValue("@Vandstandsrisiko", ps.Vandstandsrisiko);
                cmd.Parameters.AddWithValue("@Vandstand", ps.Vandstand);

                cmd.ExecuteNonQuery();
            }
        }

        public void SletPumpeSQL(int pumpestationsnr, int pumpenr)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_SletPumpe", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Pumpenr", pumpenr);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.ExecuteNonQuery();
            }
        }

        public List<double> PumpestationerVandstandSQL()
        {

            List<double> vandstandsListe = new List<double>();
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_PumpestationerVandstand", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();
     
                cmd.ExecuteNonQuery();

                SqlDataReader datareader = cmd.ExecuteReader();
                while (datareader.Read() == true)
                {
                    double vandstand = (double)datareader["Vandstand"];
                    vandstandsListe.Add(vandstand);
                }
            }
            return vandstandsListe;
        }

        public double PumpestationVandstandSQL(int pumpestationsnr)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_PumpestationVandstand", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.ExecuteNonQuery();

                SqlDataReader datareader = cmd.ExecuteReader();

                while (datareader.Read() == true)
                {
                    double vandstand = (double)datareader["Vandstand"];
                    return vandstand;
                }
            }
            throw new Exception("Vandstanden kunne ikke hentes");
        }

        public void SletPumpeStationSQL(int pumpestationsnr)
        {
            foreach (var Pumpestation in Pumpestationer())
            {
                if (Pumpestation.Pumpestationsnr == pumpestationsnr)
                {
                    foreach (var Pumpe in Pumpestation.Pumpeliste)
                    {
                        SletPumpeSQL(pumpestationsnr, Pumpe.Pumpenr);
                    }
                    break;
                }
            }

            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_SletPumpeStation", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.ExecuteNonQuery();
            }
        }

        public void StartPumpe(int pumpenr, int pumpestationsnr)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_StartogStopPumpe", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Pumpenr", pumpenr);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.Parameters.AddWithValue("@Status", true);
                cmd.ExecuteNonQuery();
            }
        }

        public void StopPumpe(int pumpenr, int pumpestationsnr)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_StartogStopPumpe", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Pumpenr", pumpenr);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.Parameters.AddWithValue("@Status", false);
                cmd.ExecuteNonQuery();
            }
        }

        public Dictionary<double, DateTime> HentVandstandsLogSQL(int pumpestationsNr)
        {
            Dictionary<double, DateTime> vandstandsLog = new Dictionary<double, DateTime>();
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_HentVandstandslog", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsNr);
                conn.Open();

                cmd.ExecuteNonQuery();

                SqlDataReader datareader = cmd.ExecuteReader();
                while (datareader.Read() == true)
                {
                    DateTime tidspunkt = (DateTime) datareader["Tidspunkt"];
                    double vandstand = (double)datareader["Vandstand"];
                    vandstandsLog.Add(vandstand, tidspunkt);
                }
            }
            return vandstandsLog;
        }

        public void NyVandstand(DateTime tidspunkt, int pumpestationsnr, decimal vandstand)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_NyVandstand", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Tidspunkt", tidspunkt);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.Parameters.AddWithValue("@Vandstand", vandstand);
                cmd.ExecuteNonQuery();
            }
        }


        private void OpretPumpeSQL(double pumpekapacitet, int pumpenr, int pumpestationsNr, bool pumpestatus)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_OpretPumpe", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Pumpekapacitet", pumpekapacitet);
                cmd.Parameters.AddWithValue("@Pumpenr", pumpenr);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsNr);
                cmd.Parameters.AddWithValue("@Pumpestatus", pumpestatus);

                cmd.ExecuteNonQuery();
            }
        }

        private void OpdatérPumpeSQL(double pumpekapacitet, int pumpenr, int pumpestationsnr, bool pumpestatus)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_OpdatérPumpe", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@Pumpekapacitet", pumpekapacitet);
                cmd.Parameters.AddWithValue("@Pumpenr", pumpenr);
                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.Parameters.AddWithValue("@Pumpestatus", pumpestatus);

                cmd.ExecuteNonQuery();
            }
        }

        private void OpdatérPumpeStationSQL(int pumpestationsnr, string netværksadresse, string ip, int port,
            string vandstandrisiko, double vandstand)
        {
            using (SqlConnection conn = new SqlConnection(CONNECTIONSTRING))
            {
                SqlCommand cmd = new SqlCommand("SP_OpdatérPumpeStation", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                conn.Open();

                cmd.Parameters.AddWithValue("@PumpestationsNr", pumpestationsnr);
                cmd.Parameters.AddWithValue("@Netværksadresse", netværksadresse);
                cmd.Parameters.AddWithValue("@IPadresse", ip);
                cmd.Parameters.AddWithValue("@Portnummer", port);
                cmd.Parameters.AddWithValue("@Vandstandsrisiko", vandstandrisiko);
                cmd.Parameters.AddWithValue("@Vandstand", vandstand);

                cmd.ExecuteNonQuery();
            }
        }

        #endregion
    }
}
